package com.glomozda.machinerepairdao.repository.machine;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.glomozda.machinerepairdao.domain.machine.Machine;

import static com.glomozda.machinerepairdao.repository.jdbc.DAOJDBCUtil.*;

@SuppressWarnings({"PMD.CommentRequired", "PMD.LawOfDemeter"})
@Repository("machineRepository")
public class MachineRepositoryJDBC implements MachineRepository {
	
	private final transient DataSource ds;

    @Autowired    
    public MachineRepositoryJDBC(final DataSource ds) {
        this.ds = ds;
    }
    
    @Override
    public Machine find(final Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Machine machine = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Machines where machines_id=?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	machine = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return machine;
    }

    @Override
    public List<Machine> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        final List<Machine> machines = new ArrayList<Machine>();

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Machines");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
            	machines.add(map(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return machines;
    }

    @Override
    public boolean create(final Machine machine) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int machinesQuantity = 0;
        
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select count(*) from Machines");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	machinesQuantity = resultSet.getInt(1);
            }
            close(preparedStatement);
            
            preparedStatement = connection.prepareStatement("INSERT INTO Machines "
                    + "(machines_id, name, country, trademark, year, times_repaired) VALUES (?, ?, ?, ?, ?, ?)");
            preparedStatement.setInt(1, machinesQuantity + 1);
            preparedStatement.setString(2, machine.getMachineName());
            preparedStatement.setString(3, machine.getMachineCountry());
            preparedStatement.setString(4, machine.getMachineTrademark());
            preparedStatement.setInt(5, machine.getMachineYear());
            preparedStatement.setInt(6, machine.getMachineTimesRepaired());
            preparedStatement.execute();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            close(preparedStatement);
            close(connection);
        }
        return false;
    }

    private Machine map(final ResultSet resultSet) throws SQLException {
    	final Machine machine = new Machine();
    	machine.setMachineID(resultSet.getInt(1));
    	machine.setMachineName(resultSet.getString(2));
    	machine.setMachineCountry(resultSet.getString(3));
    	machine.setMachineTrademark(resultSet.getString(4));
    	machine.setMachineYear(resultSet.getInt(5));
    	machine.setMachineTimesRepaired(resultSet.getInt(6));
        return machine;
    }

    @Override
    public Machine findByName(final String machineName) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Machine machine = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.
                    prepareStatement("select * from Machines where name = ?");
            preparedStatement.setString(1, machineName);
            resultSet = preparedStatement.executeQuery();
                      
            if (resultSet.next()) {
            	machine = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return machine;
    }
}
